-- @owner: @l1azzzy
-- @date: 2024/10/15
-- @testpoint: 验证gs_interval()函数作为关键字使用

--prestep1:连接到B库
@conn PrimaryDbBmode;
show sql_compatibility;

--step1:作为列名 expect:成功
drop table if exists gs_interval_test cascade;
create table gs_interval_test(gs_interval text);
insert into gs_interval_test values('0');

--step2:作为索引名 expect:成功
create index on gs_interval_test(gs_interval);

--step3:作为表名 expect:成功
drop table if exists gs_interval cascade;
create table gs_interval(gs_interval text);
drop table if exists gs_interval cascade;

--step4:作为数据库名 expect:成功
drop database if exists gs_interval;
create database gs_interval;
drop database gs_interval;

--step5:作为外部数据源名 expect:成功
drop data source if exists gs_interval;
create data source gs_interval;
drop data source gs_interval;

--step6:作为自定义函数名 expect:成功
drop table if exists t_gs_interval_0007;
create table t_gs_interval_0007(num int);
drop function if exists gs_interval(card_id int);
create function gs_interval(card_id int) returns char(50) as
$$
declare num int := 0;
begin
while num<=card_id loop
insert into t_gs_interval_0007 values(num);
num := num+1;
end loop;
return card_id;
end;
$$
language plpgsql;
/

select gs_interval(3);
drop function if exists gs_interval(card_id int);

--step7:作为存储过程名 expect:成功
create or replace procedure gs_interval() is
V1 BLOB;
begin
    IF V1 is NULL then
    raise info 'V1 is NULL';
    else
    raise info 'V1 is not NULL';
    end if;
end;
/
drop procedure if exists gs_interval();

--step8:作为角色名 expect:成功
drop role if exists gs_interval;
create role gs_interval with password 'gauss@123' valid until '2001-12-12';
drop role gs_interval;

--step9:作为用户组名 expect:成功
drop group if exists gs_interval;
create group gs_interval with password 'gauss@123';
drop group gs_interval;

--step10:作为模式名 expect:成功
drop schema if exists gs_interval;
create schema gs_interval;
drop schema gs_interval;

--step11:作为序列名 expect:成功
create sequence public.gs_interval start 100 cache 50;
drop sequence public.gs_interval;

--step12:作为用户名 expect:成功
drop user if exists gs_interval;
CREATE USER gs_interval PASSWORD 'Bigdata@123';
drop user gs_interval;

--step13:作为游标名 expect:成功
drop table if exists gs_interval_test cascade;
create table gs_interval_test(cid int,fid int);
start transaction;
cursor gs_interval for select * from gs_interval_test order by 1;
close gs_interval;end;

--step14:清理环境 expect:成功
drop table if exists gs_interval_test cascade;
drop table if exists t_gs_interval_0025;
drop table if exists t_gs_interval_0007;